package com.smartwebx.fr.util;

import java.io.ByteArrayOutputStream;
import java.util.List;
import java.util.regex.Pattern;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Style;
import com.aspose.cells.TextAlignmentType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.base.Splitter;
import com.google.common.collect.Iterables;
import com.google.common.primitives.Ints;
import com.smartwebx.fr.bean.FreeTable;
import com.smartwebx.fr.bean.MergedCellInfo;

public class AsponExcelCellUtil {
	private static ObjectMapper mapper = new ObjectMapper();

	// double 类型判断
	private static Pattern doublePattern = Pattern.compile("[0-9]+(\\.?)[0-9]+");
	// int 类型判断
	private static Pattern intPattern = Pattern.compile("[0-9]+");

	public static byte[] exportFreeTableExcel(String tableData) throws Exception {
		FreeTable freeTable = mapper.readValue(tableData, new TypeReference<FreeTable>() {
		});
		Workbook workbook = new Workbook();
		Worksheet worksheet = workbook.getWorksheets().get(0);
		Cells cells = worksheet.getCells();

		List<List<String>> data = freeTable.getData();
		int rowIndex = 0;
		for (List<String> lines : data) {
			int columnIndex = 0;
			for (String point : lines) {
				String cellName = CellsHelper.cellIndexToName(rowIndex, columnIndex);
				Cell cell = cells.get(cellName);
				if (intPattern.matcher(point).matches()) {
					cell.setValue(Integer.parseInt(point));
				} else if (doublePattern.matcher(point).matches()) {
					cell.setValue(Double.parseDouble(point));
				} else {
					cell.setValue(point);
				}
				Style style = cell.getStyle();
				style.setHorizontalAlignment(TextAlignmentType.CENTER);
				style.setVerticalAlignment(TextAlignmentType.CENTER);

				cell.setStyle(style);
				columnIndex++;
			}
			rowIndex++;
		}

		List<MergedCellInfo> mergedCellInfos = freeTable.getMergedCellInfos();
		for (MergedCellInfo mergedCellInfo : mergedCellInfos) {
			int row = mergedCellInfo.getRow();
			int col = mergedCellInfo.getCol();
			int rowspan = mergedCellInfo.getRowspan();
			int colspan = mergedCellInfo.getColspan();

			if (row < 0 || col < 0 || rowspan < 0 || colspan < 0) {
				continue;
			}
			cells.merge(row, col, rowspan, colspan);
		}
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		workbook.save(outputStream, SaveFormat.XLSX);
		outputStream.close();
		return outputStream.toByteArray();
	}

	public static byte[] exportTableExcel(String tableData) throws Exception {
		List<List<String>> result = mapper.readValue(tableData, new TypeReference<List<List<String>>>() {
		});

		Workbook workbook = new Workbook();
		Worksheet sheet = workbook.getWorksheets().get(0);
		Cells cells = sheet.getCells();

		int row = 0;
		for (List<String> lines : result) {
			int col = 0;
			for (String point : lines) {
				Iterable<String> pointSplitter = Splitter.on("::").split(point);
				if (Iterables.size(pointSplitter) != 3) {
					continue;
				}

				String text = Iterables.get(pointSplitter, 0);
				Integer rowspan = Ints.tryParse(Iterables.get(pointSplitter, 1));
				Integer colspan = Ints.tryParse(Iterables.get(pointSplitter, 2));

				for (;;) {
					String cellName = CellsHelper.cellIndexToName(row, col);
					Cell cell = cells.get(cellName);
					if (cell.isMerged()) {
						col++;
					} else {
						if (intPattern.matcher(text).matches()) {
							cell.setValue(Integer.parseInt(text));
						} else if (doublePattern.matcher(text).matches()) {
							cell.setValue(Double.parseDouble(text));
						} else {
							cell.setValue(text);
						}
						Style style = cell.getStyle();
						style.setHorizontalAlignment(TextAlignmentType.CENTER);
						style.setVerticalAlignment(TextAlignmentType.CENTER);

						cell.setStyle(style);

						cells.merge(row, col, rowspan == 0 ? 1 : rowspan, colspan == 0 ? 1 : colspan);
						break;
					}
				}
				col++;
			}
			row++;
		}

		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		workbook.save(outputStream, SaveFormat.XLSX);
		outputStream.close();
		return outputStream.toByteArray();
	}
}
